package com.cjm.one.custom.mapper;


import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.cjm.one.model.custom.dto.UserChartItem;
import com.cjm.one.model.custom.dto.UserStatsDTO;
import com.cjm.one.model.custom.entity.CustomerInfo;
import com.cjm.one.model.custom.vo.CustomerInfoVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;

/**
* @author 29289
* @description 针对表【customer_info(客户表)】的数据库操作Mapper
* @createDate 2024-11-04 16:20:11
* @Entity generator.domain.CustomerInfo
*/
@Mapper
public interface CustomerInfoMapper extends BaseMapper<CustomerInfo> {

        @Select("SELECT DATE_FORMAT(create_time, #{format}) AS dateKey," +
                "       COUNT(*) AS newUsers," +
                "       (SELECT COUNT(DISTINCT l.custom_id) " +
                "        FROM customer_login_log l " +
                "        WHERE DATE_FORMAT(l.create_time, #{format}) = dateKey " +
                "          AND l.is_deleted = 0) AS activeUsers," +
                "       (SELECT COUNT(*) " +
                "        FROM customer_info c " +
                "        WHERE DATE_FORMAT(c.create_time, #{format}) = dateKey " +
                "          AND c.user_lesson > 0) AS paidConversion " +
                "FROM customer_info " +
                "WHERE create_time BETWEEN #{start} AND #{end} " +
                "  AND is_deleted = 0 " +
                "GROUP BY dateKey " +
                "ORDER BY dateKey")
        List<UserChartItem> selectTrendData(@Param("start") LocalDateTime start,
                                            @Param("end") LocalDateTime end,
                                            @Param("format") String format);


}




